package com.wondream.myframework.app.common.util;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.Sets;
import com.wondream.myframework.app.vo.req.SearchCommonReq;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 通用查询工具类
 */
public class SearchUtils {

    private static final String[] COMP_SYMBOLS = {"=", ">", "<", ">=", "<=", "!=", "<>"};

    private static final String GET_TABLES = "select table_name from information_schema.TABLES where TABLE_SCHEMA=(select database())";

    private static final String GET_ALL_COLUMNS = "select table_name,column_name from information_schema.COLUMNS where TABLE_SCHEMA = (select database())";

    private static final String GET_COLUMNS_BY_TABLE = "select table_name,column_name from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and table_name='%s'";

    private static final String GET_FOREIGN_KEY = "SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_schema=(select database());";

    private static final String GET_FOREIGN_KEY_BY_TABLE = "SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_schema=(select database()) and table_name in(%s) and referenced_column_name is not null;";

    private static Logger logger = LoggerFactory.getLogger(SearchUtils.class);

    /**
     * 输入查询表达式进行搜索
     * @param params
     * @return
     */
    public static List<Map<String, Object>> search(SearchCommonReq params) {
        // 0.初始化
        String searchExp = StringUtils.lowerCase(params.getSearchExp());
        SqlSessionFactory sqlSessionFactory = SpringUtils.getBean(SqlSessionFactory.class);
        SqlSession sqlSession = null;
        // 1.解析前端的查询表达式，以及需要查询的列，得到涉及的字段
        Set<String> columnSets = getFieldList(searchExp);
        try {
            sqlSession = sqlSessionFactory.openSession();
            // 2.给字段加上表名
            List<Map<String, Object>> allColumns = executeQuery(sqlSession, GET_ALL_COLUMNS);
            Set<String> newColumnSets = transformField(sqlSession, allColumns, columnSets);
            // 3.获取用户配置的查询列
            String sqlQueryField = getQueryField(sqlSession);
//            sqlQueryField = transformQueryField(sqlSession, allColumns, sqlQueryField);
            // 4.查询元数据，获取表之间的外键关系，得到查询的连表语句(如：a left join b on a.id=b.user_id)
            String sqlTable = getSqlTableByColumnSet(sqlSession, newColumnSets);
            if (sqlTable==null) {
                return null;
            }
            // 5.组装query语句，select 查询列 from 连表语句 where 查询表达式
            String sql = String.format("select %s from %s where %s ", sqlQueryField, sqlTable, searchExp);
            // 6.查询数据
            List<Map<String, Object>> queryResult = executeQuery(sqlSession, sql);
            return queryResult;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
        return null;
    }

//    private static String transformQueryField(SqlSession sqlSession, List<Map<String, Object>> allColumns, String sqlQueryField) {
//        if(StringUtils.equals("*", sqlQueryField)){
//            return sqlQueryField;
//        }
//        String[] split = StringUtils.split(sqlQueryField, ",");
//        Set<String> fullColumnSets = columnSets.stream().filter(x -> x.contains(".")).map(x -> StringUtils.lowerCase(x)).collect(Collectors.toSet());
//        Set<String> halfColumnSets = columnSets.stream().filter(x -> !x.contains(".")).map(x -> StringUtils.lowerCase(x)).collect(Collectors.toSet());
//        if (halfColumnSets.size()<1) {
//            return fullColumnSets;
//        }
//        for (Map<String, Object> column : allColumns) {
//            String tableName = StringUtils.lowerCase((String) column.get("table_name"));
//            String columnName = StringUtils.lowerCase((String) column.get("column_name"));
//            if(halfColumnSets.contains(columnName)){
//                fullColumnSets.add(String.format("%s.%s", tableName, columnName));
//            }
//        }
//        return fullColumnSets;
//    }

    /**
     * 获取用户配置的查询列
     * @param sqlSession
     * @return
     */
    private static String getQueryField(SqlSession sqlSession) {
//        return "cs_field_group.id,cs_field_group.group_name,cs_field.field_id,cs_field.field_name,cs_field_ext.ext";
        return "cs_field_group.*,cs_field.*,cs_field_ext.*";
    }

    /**
     * 给字段加上表名
     * @param sqlSession
     * @param columnSets
     * @return
     */
    private static Set<String> transformField(SqlSession sqlSession, List<Map<String, Object>> allColumns, Set<String> columnSets) {
        Set<String> fullColumnSets = columnSets.stream().filter(x -> x.contains(".")).map(x -> StringUtils.lowerCase(x)).collect(Collectors.toSet());
        Set<String> halfColumnSets = columnSets.stream().filter(x -> !x.contains(".")).map(x -> StringUtils.lowerCase(x)).collect(Collectors.toSet());
        if (halfColumnSets.size()<1) {
            return fullColumnSets;
        }
        for (Map<String, Object> column : allColumns) {
            String tableName = StringUtils.lowerCase((String) column.get("table_name"));
            String columnName = StringUtils.lowerCase((String) column.get("column_name"));
            if(halfColumnSets.contains(columnName)){
                fullColumnSets.add(String.format("%s.%s", tableName, columnName));
            }
        }
        return fullColumnSets;
    }

    /**
     * 查询元数据，获取表之间的外键关系，得到查询的连表语句(如：a left join b on a.id=b.user_id)
     * @param sqlSession
     * @param columnSets
     * @return
     */
    private static String getSqlTableByColumnSet(SqlSession sqlSession, Set<String> columnSets) {
        try {
            // 1.从字段中获取涉及到的表
            Set<String> tableSets = new HashSet<>();
            for (String x : columnSets) {
                tableSets.add(StringUtils.split(x, ".")[0]);
            }
            // 2.单表查询的话，直接返回表信息
            if(tableSets.size()==1){
                return tableSets.iterator().next();
            }
            // 3.获取表外键信息
            Set<String> formatedTableSets = tableSets.stream().map(x -> String.format("'%s'", x)).collect(Collectors.toSet());
            List<Map<String, Object>> foreignKeys = executeQuery(sqlSession, String.format(GET_FOREIGN_KEY_BY_TABLE, StringUtils.join(formatedTableSets.toArray(), ",")));
            // 4.获取主表
            Set<String> tableNames = foreignKeys.stream().map(o -> (String)o.get("table_name")).collect(Collectors.toSet());
            Sets.SetView<String> difference = Sets.difference(tableSets, tableNames);
            if (difference.size() != 1) {
                return null;
            }
            String mainTable = difference.iterator().next();
            String sqlTable = mainTable;
            // 5.拼接连表语句
            sqlTable = getJoinTableSql(mainTable, sqlTable, foreignKeys);
            return sqlTable;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 拼接连表语句
     * @param currentTable
     * @param sqlTable
     * @param foreignKeys
     * @return
     */
    private static String getJoinTableSql(String currentTable, String sqlTable, List<Map<String, Object>> foreignKeys) {
        List<Map<String, Object>> relativeTable = foreignKeys.stream().filter(x -> StringUtils.equals(currentTable, (String) x.get("referenced_table_name"))).collect(Collectors.toList());
        if (CollectionUtils.isEmpty(relativeTable)) {
            return sqlTable;
        }
        // 1.先拼接与当前表直接关联的表
        for (Map<String, Object> table : relativeTable) {
            String tableName = (String) table.get("table_name");
            String columnName = (String) table.get("column_name");
            String referencedTableName = (String) table.get("referenced_table_name");
            String referencedColumnName = (String) table.get("referenced_column_name");
            sqlTable = String.format("%s left join %s on %s.%s=%s.%s ", sqlTable, tableName, tableName, columnName, referencedTableName, referencedColumnName);
        }
        // 2.再拼接间接关联的表
        for (Map<String, Object> table : relativeTable) {
            String tableName = (String) table.get("table_name");
            sqlTable = getJoinTableSql(tableName, sqlTable, foreignKeys);
        }
        return sqlTable;
    }

    /**
     * 执行查询语句
     * @param sqlSession
     * @param sql
     * @return
     */
    private static List<Map<String, Object>> executeQuery(SqlSession sqlSession, String sql) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = sqlSession.getConnection().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();   //获得列数
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(StringUtils.lowerCase(md.getColumnName(i)), resultSet.getObject(i));
                }
                resultList.add(rowData);
            }
            logger.info(JSON.toJSONString(resultList));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultList;
    }

    /**
     * 解析前端的查询表达式，以及需要查询的列，得到涉及的字段
     * @param queryExp
     * @return
     */
    public static Set<String> getFieldList(String queryExp) {
        Stack<String> stack = new Stack<>();
        int end = 0;
        int start = 0;
        List<String> condition = new ArrayList<>();
        boolean betweenFlag = false;
        boolean inFlag = false;
        while (end < queryExp.length()) {

            if (queryExp.substring(end).startsWith("between")) {
                betweenFlag = true;
            }
            if (queryExp.substring(end).startsWith("in")) {
                inFlag = true;
            }

            if (queryExp.substring(end).startsWith("(")) {
                if (inFlag) {
                    end++;
                    continue;
                }
                stack.push("(");
                end++;
                start = end;
                continue;
            }
            if (queryExp.substring(end).startsWith(")")) {
                if (inFlag) {
                    end++;
                    inFlag = false;
                    continue;
                }
                String substring = queryExp.substring(start, end);
                condition.add(substring);
                end++;
                start = end;
                continue;
            }
            if (queryExp.substring(end).startsWith("and")) {
                if (betweenFlag) {
                    end++;
                    betweenFlag = false;
                    continue;
                }
                String substring = queryExp.substring(start, end);
                condition.add(substring);
                end += 3;
                start = end;
                continue;

            }
            if (queryExp.substring(end).startsWith("or")) {
                String substring = queryExp.substring(start, end);
                condition.add(substring);
                end += 2;
                start = end;
                continue;
            }
            end++;
        }
        if (start != end) {
            String substring = queryExp.substring(start, end);
            condition.add(substring);
        }
        Set<String> columnSets = new HashSet<>();
        for (String str :
                condition) {
            if (StringUtils.isBlank(str)) {
                continue;
            }
            String columnName = getColumnNameByCondition(str.trim());
            if (StringUtils.isBlank(columnName)) {
                continue;
            }
//            System.out.println(str.trim());
//            System.out.println(columnName);
            columnSets.add(StringUtils.lowerCase(columnName));

        }
//        System.out.println(JSON.toJSONString(columnSets));
        return columnSets;

    }

    /**
     * 从表达式中获取字段名
     * @param str
     * @return
     */
    private static String getColumnNameByCondition(String str) {
        String symbol = containsCompareSymbols(str);
        if (StringUtils.isBlank(symbol)) {
            return str.split(" ")[0];
        }

        String[] split = str.split(symbol);
        for (String s : split) {
            if (s.startsWith("'") || StringUtils.isNumeric(s.trim())) {
                continue;
            }
            return s.trim();
        }
        return null;
    }

    /**
     * 判断是否包含算术运算符
     * @param str
     * @return
     */
    private static String containsCompareSymbols(String str) {
        for (String compSymbol : COMP_SYMBOLS) {
            if (str.contains(compSymbol)) {
                return compSymbol;
            }
        }
        return null;
    }

}
